package cs757;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Vector;

public class DatabaseConnector {
//	private static String SERVER = "ec2-107-22-45-3.compute-1.amazonaws.com";
////	private static String SERVER = "localhost";
	private static String PORT = "3306";
//	private static String URL = "jdbc:mysql://"+SERVER+":"+PORT+"/";	
	
	protected final static String ADMIN_USER_NAME = "admin";
	
	/**
	 * Query strings used for sending SQL statements to the database
	 */
	protected static final String STUDENTS_QUERY = "SELECT * FROM UWDB.students";
	protected static final String PROFESSORS_QUERY = "SELECT * FROM UWDB.professors";
	protected static final String PROJECTS_QUERY = "SELECT * FROM UWDB.project";
	protected static final String PROJECTS_QUERY_ORDER_ID = PROJECTS_QUERY+" ORDER BY ipid";
	protected static final String PROJECTS_QUERY_ORDER_BUDGET = PROJECTS_QUERY+" ORDER BY fbudget DESC";
	protected static final String PROJECTS_MOST_FUNDED = "SELECT ipid, fbudget FROM UWDB.project WHERE fbudget in (SELECT MAX(fbudget) FROM UWDB.project)";
	protected static final String PROJECTS_BY_DEPARTMENT =
			"SELECT a.ipid, a.csponsor, a.tenddate, a.tstartdate, a.fbudget, b.cpssn, c.cname, d.idnum, e.cdname" +
			" FROM UWDB.project a, UWDB.manages b, UWDB.professors c, UWDB.work_dept d, UWDB.dept e" +
			" WHERE a.ipid=b.ipid AND b.cpssn=c.cpssn AND c.cpssn=d.cpssn AND d.idnum=e.idnum AND d.idnum=";
	
	protected static final String INSERT_STUDENT_QUERY = "INSERT INTO UWDB.students VALUES";
	protected static final String INSERT_STUDENT_ADVISOR_QUERY = "INSERT INTO UWDB.advisor VALUES";
	protected static final String INSERT_PROFESSOR_QUERY = "INSERT INTO UWDB.professors VALUES";
	protected static final String INSERT_PROJECT = "INSERT INTO UWDB.project VALUES";
	protected static final String INSERT_GROUP_MEMBER_STUDENT = "INSERT INTO UWDB.supervises VALUES";
	protected static final String INSERT_GROUP_MEMBER_PROFESSOR = "INSERT INTO UWDB.works_in VALUES";
	protected static final String INSERT_DEPT_MEMBER_PROFESSOR = "INSERT INTO UWDB.work_dept VALUES";
	protected static final String INSERT_GROUP_MEMBER_PROFESSOR_MANAGER = "INSERT INTO UWDB.manages VALUES";
	
	protected static final String DELETE_STUDENT_QUERY = "DELETE FROM UWDB.students WHERE cgssn=";
	protected static final String DELETE_STUDENT_ADVISEE_QUERY = "DELETE FROM UWDB.advisor WHERE csssn=";
	protected static final String DELETE_PROFESSOR_QUERY = "DELETE FROM UWDB.professors WHERE cpssn=";
	protected static final String DELETE_PROJECT_QUERY = "DELETE FROM UWDB.project WHERE ipid=";
	protected static final String DELETE_GROUP_MEMBER_PROFESSOR_QUERY = "DELETE FROM UWDB.works_in WHERE cpssn=";
	protected static final String DELETE_GROUP_MEMBER_STUDENT_QUERY = "DELETE FROM UWDB.supervises WHERE cgssn=";
	protected static final String DELETE_GROUP_MEMBER_PROFESSOR_MANAGER = "DELETE FROM UWDB.manages WHERE ipid=";
	
	protected static final String UPDATE_QUERY = "UPDATE UWDB.project SET fbudget=";
	protected static final String UPDATE_QUERY_WHERE = "WHERE ipid=";
	
	protected static final String DEPT_ID_QUERY = "SELECT idnum, cdname FROM UWDB.dept";
	protected static final String STUDENT_WORKS_AVAILABLE = "SELECT cgssn, cgradname FROM UWDB.students";
	protected static final String STUDENT_WORKS_QUERY = "SELECT a.cgssn, b.cgradname FROM UWDB.supervises a, UWDB.students b WHERE a.cgssn = b.cgssn AND a.ipid=";
	protected static final String PROFESSOR_WORKS_AVAILABLE = "SELECT cpssn, cname FROM UWDB.professors";
	protected static final String PROFESSOR_WORKS_QUERY = "SELECT a.cpssn, b.cname FROM UWDB.works_in a, UWDB.professors b WHERE a.cpssn = b.cpssn AND a.ipid=";
	
	//Queries for the encrypted students relation
	protected static final String DROP_ENCRYPTED_STUDENTS = "DROP TABLE IF EXISTS UWDB.studentsencrypted";
	protected static final String CREATE_ENCRYPTED_STUDENTS = "CREATE  TABLE UWDB.studentsencrypted (cgssn VARCHAR(9) NOT NULL , cage VARCHAR(16) NOT NULL , cgradname VARCHAR(100) NOT NULL , cdegree VARCHAR(50) NOT NULL , imajor INT NOT NULL , PRIMARY KEY (cgssn))";
	protected static final String INSERT_ENCRYPTED_STUDENT_QUERY = "INSERT INTO UWDB.studentsencrypted VALUES";
	protected static final String ENCRYPTED_STUDENTS_QUERY = "SELECT * FROM UWDB.studentsencrypted";
	
	protected static final String IS_PROFESSOR_QUERY = "SELECT * FROM UWDB.professors WHERE cpssn=";
	protected static final String IS_PROFESSOR_PROJECT_MANAGER_PT1 =
			"SELECT b.cpssn" +
			" FROM UWDB.project a, UWDB.manages b, UWDB.professors c" +
			" WHERE a.ipid=b.ipid AND b.cpssn=c.cpssn AND a.ipid=";
	protected static final String IS_PROFESSOR_PROJECT_MANAGER_PT2 =
			" AND c.cpssn=";

	/**
	 * Reference to GUI for logging in to the database
	 */
	private LoginGUI loginGUI;
	
	/**
	 * Column names of tables we query
	 */
	protected static final 	Vector<String> columnNamesStudents = new Vector<String>(Arrays.asList(new String[]{
			"SSN",
			"Age",
			"Name",
			"Degree",
			"Major"}));
	
	protected static final 	Vector<String> columnNamesProfessors = new Vector<String>(Arrays.asList(new String[]{
			"SSN",
			"Name",
			"Age",
			"Rank",
			"Specialty"}));
	
	protected static final 	Vector<String> columnNamesProjects = new Vector<String>(Arrays.asList(new String[]{
			"ID",
			"Sponsor",
			"End Date",
			"Start Date",
			"Funding"}));
	
	protected static final 	Vector<String> columnNamesProjectsByDepartment = new Vector<String>(Arrays.asList(new String[]{
			"ID",
			"Sponsor",
			"End Date",
			"Start Date",
			"Funding",
			"Manger SSN",
			"Manager Name",
			"Dept. ID",
			"Dept. Name"}));

	
	/**
	 * Store the user ID and connection object for referencing by the query
	 */
	private String userID;
	private Connection conn;
	
	
	/**
	 * Attempt to connect to the database through JDBC
	 * @return
	 */
	protected Connection getConnection(String userID, String password, String server){
		Connection con = null;
		
		try {		
			Class.forName("com.mysql.jdbc.Driver");

			String url = "jdbc:mysql://"+server+":"+PORT+"/";
			con = DriverManager.getConnection(url, userID, password);

		} catch(SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			e.printStackTrace();
		}
		catch(java.lang.ClassNotFoundException e) {
			System.out.print("ClassNotFoundException: ");
			System.out.println(e.getMessage());
		}
		
		return con;
	}
	
	/**
	 * Execute a SELECT query to lookup data in the database
	 * @param query - Query string , from the static values in DatabaseConnector
	 * @param numColumns - number of columns in the table we are querying
	 * @return  A 2-Dimensional vector of values, rows by columns, where the outer vector is the collection
	 * of rows (records) returned, and each row has an inner vector, where the inner vector is the collection
	 * of values for the table's columns associated with that record
	 * @throws SQLException
	 */
	protected Vector<Vector<Object>> executeQuery(String query, int numColumns) throws SQLException{	
	
		Statement stmt = conn.createStatement();
		ResultSet results = stmt.executeQuery(query);
		Vector<Vector<Object>> rowData = new Vector<Vector<Object>>();

		while (results.next()) {
			Vector<Object> row = new Vector<Object>();

			for (int i=1; i <= numColumns; i++){
				row.add(results.getString(i));
			}

			rowData.add(row);
		}
		stmt.close();
		
		return rowData;
	}
	
	/**
	 * Execute an INSERT statement to add a record to the database
	 * @param insert - Insert statement string, from static values in DatabaseConnector
	 * @param values - values of the record to be inserted
	 * @throws SQLException
	 */
	protected void executeInsert(String insert, Object[] values) throws SQLException{
		StringBuilder insertStmt = new StringBuilder(insert+"(");
		for (int i=0; i<values.length; i++){
			if(values[i] instanceof String){
				insertStmt.append("'");
			}
			
			insertStmt.append(values[i]);
			
			if(values[i] instanceof String){
				insertStmt.append("'");
			}
			if (i< (values.length -1) ){
				insertStmt.append(", ");
			}
		}
		insertStmt.append(")");

		executeStatement(insertStmt.toString());
	}
	
	/**
	 * Execute a DELETE statement on the database
	 * @param deleteStatement - Delete statement string, from static values in DatabaseConnector
	 * @param key - key used to identify the record to delete
	 * @throws SQLException
	 */
	protected void executeDelete(String deleteStatement, String key) throws SQLException{
		String deleteStmt = deleteStatement+key;
		executeStatement(deleteStmt);
	}
	
	/**
	 * Execute an UPDATE statement on the database
	 * @param updateStatement - First part of update statement string, from static values in DatabaseConnector
	 * 							Describes table and field we are updating
	 * @param updateWhereStatement - Second part of update statement string, from static values in DatabaseConnector
	 * 								Describes key value used to determine what record to update
	 * @param value - The value for the field we are updating
	 * @param key - The primary key of the record used to tell the DB what row to provided the new value for
	 * @throws SQLException
	 */
	protected void executeUpdate(String updateStatement, String updateWhereStatement, String value, String key) throws SQLException{
		String updateStmt = updateStatement+value+" "+updateWhereStatement+key;
		executeStatement(updateStmt);
	}
	
	/**
	 * Drops the 'graduatesencrypted' table to make room for newly encrypted students.
	 * @throws SQLException
	 */
	protected void executeDropEncryptedStudents() throws SQLException {
		executeStatement(DROP_ENCRYPTED_STUDENTS);
	}
	
	/**
	 * Creates the 'graduatesencrypted' table to make room for newly encrypted students.
	 * @throws SQLException
	 */
	protected void executeCreateEncryptedStudents() throws SQLException {
		executeStatement(CREATE_ENCRYPTED_STUDENTS);
	}
	
	/**
	 * Execute a generic SQL statement on the database
	 * @param statement
	 * @throws SQLException
	 */
	private void executeStatement(String statement) throws SQLException{
		Statement stmt = conn.createStatement();
		stmt.execute(statement);
		stmt.close();
	}
	
	/**
	 * Checks if the current user is a professor
	 */
	protected boolean isUserAProfessor()
	{	
		boolean isProfessor = false;
		try {
			Vector<Vector<Object>> results = executeQuery(IS_PROFESSOR_QUERY+userID, columnNamesProfessors.size());
			if (results.size() > 0){
				isProfessor = true;
			}
		} catch (SQLException e) {
			isProfessor = false;
		}
		return isProfessor;
	}
	
	/**
	 * Checks if the current user is the project's manager
	 */
	protected boolean isUserAProjectManager(String projectID)
	{	
		boolean isManager = false;
		try {
			Vector<Vector<Object>> results = executeQuery(IS_PROFESSOR_PROJECT_MANAGER_PT1+projectID+IS_PROFESSOR_PROJECT_MANAGER_PT2+"'"+userID+"'", 1);
			if (results.size() > 0){
				isManager = true;
			}
		} catch (SQLException e) {
			isManager = false;
		}
		return isManager;
	}
	
	
	/**
	 * Launch login window to begin login process
	 */
	private void login(){
		loginGUI = new LoginGUI();
		loginGUI.setModal(true);
		loginGUI.launchLoginWindow(this);
	}
	
	/**
	 * Launch main GUI for executing queries
	 */
	private void launchMainGUI() {
		MainGUI mainGUI = new MainGUI();
		mainGUI.setUserID(userID);
		mainGUI.launch(this);
		
	}
	
	/**
	 * @return the userID
	 */
	public String getUserID() {
		return userID;
	}

	/**
	 * @param userID the userID to set
	 */
	public void setUserID(String userID) {
		this.userID = userID;
	}

	/**
	 * @return the conn
	 */
	public Connection getConn() {
		return conn;
	}

	/**
	 * @param conn the conn to set
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	
	/**
	 * Starting point for the assignment's programs
	 * 
	 * RUN THIS TO RUN THE APPLICATION
	 * 
	 * @param args
	 */
	public static void main(String[] args){
		DatabaseConnector dbConn = new DatabaseConnector();
		dbConn.login();
		if (dbConn.loginGUI.isOk()){
			dbConn.launchMainGUI();
		}
		
	}


}
